package com.hxt.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excel处理工具 概念-> 表头数据:报表的表头 行内数据:表头以下的数据 功能:动态生成单级，多级Excel表头
 * 备注：tree型结构数据的root节点的id必须为零（0） Created by wtj on 2018/3/2.
 * 
 * @param <T>
 */
public class ExcelTool<T> {

	private HSSFWorkbook workbook;// excel 对象
	private String title; // 表格标题
	private int colWidth = 20; // 单元格宽度
	private int rowHeight = 20;// 单元格行高度
	private HSSFCellStyle styleHead; // 表头样式
	private HSSFCellStyle styleBody; // 主体样式
	private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 日期格式化,默认yyyy-MM-dd HH:mm:ss

	/**
	 * 无参数 初始化 对象
	 */
	public ExcelTool() {
		this.title = "sheet1";
		this.workbook = new HSSFWorkbook();
		init(0);
	}

	/**
	 * 有参数 初始化 对象
	 * 
	 * @param title
	 * @param colWidth
	 * @param rowHeight
	 * @param dateFormat
	 */
	public ExcelTool(String title, int colWidth, int rowHeight, String dateFormat) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		this.title = title;
		this.workbook = new HSSFWorkbook();
		sdf = new SimpleDateFormat(dateFormat);
		init(0);
	}

	public ExcelTool(String title, int colWidth, int rowHeight) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		this.title = title;
		this.workbook = new HSSFWorkbook();
		init(0);
	}

	public ExcelTool(String title, int colWidth, int rowHeight, int flag) {
		this.colWidth = colWidth;
		this.rowHeight = rowHeight;
		this.title = title;
		this.workbook = new HSSFWorkbook();
		init(flag);
	}

	public ExcelTool(String title) {
		this.title = title;
		workbook = new HSSFWorkbook();
		init(0);
	}

	/** ExcelTool 属性 get、set 方法 开始 */
	public int getColWidth() {
		return colWidth;
	}

	public void setColWidth(int colWidth) {
		this.colWidth = colWidth;
	}

	public int getRowHeight() {
		return rowHeight;
	}

	public void setRowHeight(int rowHeight) {
		this.rowHeight = rowHeight;
	}

	public HSSFWorkbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(HSSFWorkbook workbook) {
		this.workbook = workbook;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public HSSFCellStyle getStyleHead() {
		return styleHead;
	}

	public void setStyleHead(HSSFCellStyle styleHead) {
		this.styleHead = styleHead;
	}

	public HSSFCellStyle getStyleBody() {
		return styleBody;
	}

	public void setStyleBody(HSSFCellStyle styleBody) {
		this.styleBody = styleBody;
	}

	/** ExcelTool 属性 get、set 方法 结束 */
	// 内部统一调用的样式初始化
	private void init(int styleFlag) {
		styleHead = workbook.createCellStyle();
		styleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		styleHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		styleHead.setRightBorderColor(HSSFColor.BLACK.index);
		styleHead.setBottomBorderColor(HSSFColor.BLACK.index);
		switch (styleFlag) {
		case 1:
			styleBody = workbook.createCellStyle();
			styleBody.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中ALIGN_CENTER
			styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
			styleBody.setRightBorderColor(HSSFColor.BLACK.index);
			styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
			styleBody.setBorderRight((short) 1);// 边框的大小
			styleBody.setBorderBottom((short) 1);// 边框的大小
			break;
		default:
			styleBody = workbook.createCellStyle();
			styleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中ALIGN_CENTER
			styleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
			styleBody.setRightBorderColor(HSSFColor.BLACK.index);
			styleBody.setBottomBorderColor(HSSFColor.BLACK.index);
			styleBody.setBorderRight((short) 1);// 边框的大小
			styleBody.setBorderBottom((short) 1);// 边框的大小
			break;
		}
	}


	/**
	 * 根据list 来创建单元格 暂时没有用
	 * 
	 * @param row
	 * @param j
	 * @param finame
	 * @param t
	 */
	private void createColl(HSSFRow row, int j, String finame, T t) {
		HSSFCell cell = row.createCell(j); // 创建单元格
		cell.setCellStyle(styleBody); // 设置单元格样式
		String text = "";
		if (t instanceof List) {
			List<Map> temp = (List<Map>) t;
			if (j >= temp.size())
				return;
			text = String.valueOf(temp.get(j).get(finame) == null ? "" : temp.get(j).get(finame));
		}
		HSSFRichTextString richString = new HSSFRichTextString(text);
		cell.setCellValue(richString);
	}

	/**
	 * 把column的columnList整理成一个list<column> 过滤表头的脏数据
	 * 
	 * @param list    表头数据
	 * @param listCol 返回新的list
	 * @return List<column>
	 */
	private void getColumnList(List<Column> list, List<Column> listCol) {
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getFieldName() != null) {
				listCol.add(list.get(i));
			}
			List<Column> listChilren = list.get(i).getListTpamscolumn();
			if (listChilren.size() > 0) {
				getColumnList(listChilren, listCol);
			}
		}
	}

	/**
	 * 保存Excel到InputStream，此方法适合web导出excel
	 * 
	 * @param workbook
	 * @return
	 */
	private InputStream save(HSSFWorkbook workbook) {
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		try {
			workbook.write(bos);
			InputStream bis = new ByteArrayInputStream(bos.toByteArray());
			return bis;
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	/**
	 * 保存excel到本机指定的路径
	 * 
	 * @param workbook
	 * @param filePath
	 * @throws IOException
	 */
	private void save(HSSFWorkbook workbook, String filePath) {
		File file = new File(filePath);
		if (!file.getParentFile().exists()) {
			file.getParentFile().mkdirs();
		}
		FileOutputStream fOut = null;
		try {
			fOut = new FileOutputStream(file);
			workbook.write(fOut);
			fOut.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			if (null != fOut)
				fOut.close();
		} catch (Exception e1) {
		}
	}


	/**
	 * 时间转换
	 * 
	 * @param date
	 * @return String
	 */
	private String parseDate(Date date) {
		String dateStr = "";
		try {
			dateStr = sdf.format(date);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dateStr;
	}

	/**
	 * 根据数据的行数和列数，在excel创建单元格cell
	 * 
	 * @param sheetCo excel分页
	 * @param r       excel 行数
	 * @param c       excel 列数
	 * @return
	 */
	public HSSFSheet createHead(HSSFSheet sheetCo, int r, int c) {
		for (int i = 0; i < r; i++) {
			HSSFRow row = sheetCo.createRow(i);
			for (int j = 0; j < c; j++) {
				HSSFCell cell = row.createCell(j);
			}
		}
		return sheetCo;
	}

	/**
	 * 使用递归 在excel写入表头数据 支持单级，多级表头的创建
	 * 
	 * @param listTpamscolumn 表头数据
	 * @param sheetCo         哪个分页
	 * @param rowIndex        当前Excel的第几行
	 */
	public void createHead(List<Column> listTpamscolumn, HSSFSheet sheetCo, int rowIndex) {
		HSSFRow row = sheetCo.getRow(rowIndex);
		int len = listTpamscolumn.size();// 当前行 有多少列
		for (int i = 0; i < len; i++) {// i是headers的索引，n是Excel的索引 多级表头
			Column tpamscolumn = listTpamscolumn.get(i);
			// 创建这一行的第几列单元格
			int r = tpamscolumn.getRow();
			int rLen = tpamscolumn.getrLen();
			int c = tpamscolumn.getCol();
			int cLen = tpamscolumn.getcLen();
			int endR = r + rLen;
			int endC = c + cLen;
			if (endC > c) {
				endC--;
			}
			HSSFCell cell = row.getCell(c);
			cell.setCellStyle(styleHead); // 设置表头样式
			HSSFRichTextString text = new HSSFRichTextString(tpamscolumn.getContent());
			cell.setCellValue(text);
			// 合并单元格
			CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC);
			sheetCo.addMergedRegion(cra);
			if (tpamscolumn.isHasChilren()) {
				rowIndex = r + 1;
				createHead(tpamscolumn.getListTpamscolumn(), sheetCo, rowIndex);
			}
		}
	}


	
	/**
	 * 设置基础的参数
	 * 
	 * @param list
	 */
	public static void setParm(List<Column> list) {
		int row = 0;// excel第几行
		int rLen = 0; // excel 跨多少行
		int tree_step = 0;
		for (int i = 0; i < list.size(); i++) {
			Column poit = list.get(i);
			tree_step = TreeTool.getTreeStep(list, poit.getPid(), 0);
			poit.setTree_step(tree_step);
			poit.setRow(tree_step);// 设置第几行
			// 判断是否有节点
			boolean hasCh = TreeTool.hasChild(list, poit);
			poit.setHasChilren(hasCh);
			if (hasCh) {
				poit.setrLen(0);// 设置跨多少行
			} else {
				int maxIdex = TreeTool.getMaxStep(list, "0") - 1;
				if (tree_step < maxIdex) {
					rLen = maxIdex - tree_step;
					poit.setrLen(rLen);
				}
			}
			if ("0".equals(poit.getPid())) {
				poit.setTotalRow(TreeTool.getMaxStep(list, "0"));
				poit.setTotalCol(TreeTool.getDownChilren(list, "0"));
			}

		}
	}

	/**
	 * 设置基础的参数
	 * 
	 * @param list
	 * @param treeList
	 * @param pcIndex
	 */
	public static void setColNum(List<Column> list, List<Column> treeList, int pcIndex) {
		int col = pcIndex;// excel第几列
		int cLen;// excel跨多少列
		for (int i = 0; i < treeList.size(); i++) {
			Column poit = treeList.get(i);
			if (treeList.size() <= 1) {
				poit.setCol(col);
			} else {
				for (int j = 0; j < i; j++) {
					Column poitJ = treeList.get(j);
					cLen = TreeTool.getDownChilren(list, poitJ.getId());
					if (cLen == 0 || cLen == 1) {
						col++;
					} else {
						col += cLen;
					}
				}
				poit.setCol(col);
				col = pcIndex;
			}
			cLen = TreeTool.getDownChilren(list, poit.getId());
			poit.setcLen(cLen);// 设置跨多少列
			if (poit.getListTpamscolumn().size() > 0) {
				pcIndex = poit.getCol();
				setColNum(list, poit.getListTpamscolumn(), pcIndex);
			}
		}
	}

//========上部分是导出excel的使用（生成excel），下部分是解析excel，由于excel导入==================================================================================================================================

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell 单元格
	 * @return
	 */
	public static String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) { // 判断当前Cell的Type

			case HSSFCell.CELL_TYPE_NUMERIC: // 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} else { // 如果是纯数字
					cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			case HSSFCell.CELL_TYPE_STRING: // 如果当前Cell的Type为STRIN
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default: // 默认的Cell值
				cellvalue = "";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbookType(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (".xlsx".equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("导入格式错误");
		}
		return wb;
	}

	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * 
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	public static String getStringCellValue(Cell cell) {
		String strCell = "";
		if (cell == null) {
			return "";
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue().trim();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue()).trim();
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue()).trim();
			break;
		case Cell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		return strCell;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row    行下标
	 * @param column 列下标
	 * @return
	 */
	public boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row    行下标
	 * @param column 列下标
	 * @return
	 */
	public String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getStringCellValue(fCell);
				}
			}
		}
		return "";
	}

	/**
	 * 获取excel的值 返回的 List<List<String>>的数据结构
	 * 
	 * @param fileUrl  文件路径
	 * @param sheetNum 工作表（第几分页[1,2,3.....]）
	 * @return List<List<String>>
	 */
	public List<List<String>> getExcelValues(String fileUrl, int sheetNum) throws Exception {
		List<List<String>> values = new ArrayList<List<String>>();
		File file = new File(fileUrl);
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		int sheetCount = sheetNum - 1; // workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
		// int sheetCount1= workbook.getNumberOfSheets();
		Sheet sheet = workbook.getSheetAt(sheetCount); // 读取第几个工作表sheet
		int rowNum = sheet.getLastRowNum();// 有多少行
		for (int i = 1; i <= rowNum; i++) {
			Row row = sheet.getRow(i);// 第i行
			if (row == null) {// 过滤空行
				continue;
			}
			List<String> list = new ArrayList<>();
			int colCount = sheet.getRow(0).getLastCellNum();// 用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
			for (int j = 0; j < colCount; j++) {// 第j列://+1是因为最后一列是空 也算进去
				Cell cell = row.getCell(j);
				String cellValue;
				boolean isMerge = false;
				if (cell != null) {
					isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
				}
				// 判断是否具有合并单元格
				if (isMerge) {
					cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
				} else {
					cellValue = getStringCellValue(cell);
				}
				list.add(cellValue);
			}
			values.add(list);
		}
		return values;
	}

	/**
	 * 判断整行是否为空
	 * 
	 * @param row    excel得行对象
	 * @param maxRow 有效值得最大列数
	 */
	private static boolean CheckRowNull(Row row, int maxRow) {
		int num = 0;
		for (int j = 0; j < maxRow; j++) {
			Cell cell = row.getCell(j);
			if (cell == null || cell.equals("") || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				num++;
			}
		}
		if (maxRow == num)
			return true;
		return false;
	}

	/**
	 * 根据sheet数获取excel的值 返回List<List<Map<String,String>>>的数据结构
	 * 
	 * @param fileUrl  文件路径
	 * @param sheetNum 工作表（第几分页[1,2,3.....]）
	 * @return List<List<Map<String,String>>>
	 */
	public List<List<Map<String, String>>> getExcelMapVal(String fileUrl, int sheetNum) throws Exception {
		List<List<Map<String, String>>> values = new ArrayList<List<Map<String, String>>>();
		File file = new File(fileUrl);
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		int sheetCount = sheetNum - 1; // workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页
		// int sheetCount1= workbook.getNumberOfSheets();
		Sheet sheet = workbook.getSheetAt(sheetCount); // 读取第几个工作表sheet
		int rowNum = sheet.getLastRowNum();// 有多少行
		Row rowTitle = sheet.getRow(0);// 第i行
		int colCount = sheet.getRow(0).getLastCellNum();// 用表头去算有多少列，不然从下面的行计算列的话，空的就不算了
		for (int i = 1; i <= rowNum; i++) {
			Row row = sheet.getRow(i);// 第i行
			if (row == null || CheckRowNull(row, colCount)) {// 过滤空行
				continue;
			}
			List<Map<String, String>> list = new ArrayList<Map<String, String>>();
			for (int j = 0; j < colCount; j++) {// 第j列://+1是因为最后一列是空 也算进去
				Map<String, String> map = new HashMap<>();
				Cell cell = row.getCell(j);
				Cell cellTitle = rowTitle.getCell(j);
				String cellValue;
				String cellKey = getStringCellValue(cellTitle);
				boolean isMerge = false;
				if (cell != null) {
					isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
				}
				// 判断是否具有合并单元格
				if (isMerge) {
					cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex());
				} else {
					cellValue = getStringCellValue(cell);
				}
				map.put(cellKey, cellValue);
				list.add(map);
			}
			values.add(list);
		}
		return values;
	}

	/**
	 * 获取当前excel的工作表sheet总数
	 * 
	 * @param fileUrl
	 * @return
	 * @throws Exception
	 */
	public int hasSheetCount(String fileUrl) throws Exception {
		File file = new File(fileUrl);
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		int sheetCount = workbook.getNumberOfSheets();
		System.out.println(sheetCount);
		return sheetCount;
	}

	/**
	 * Excel column index begin 1
	 * 
	 * @param colStr
	 * @param length
	 * @return
	 */
	public static int excelColStrToNum(String colStr, int length) {
		int num = 0;
		int result = 0;
		for (int i = 0; i < length; i++) {
			char ch = colStr.charAt(length - i - 1);
			num = (int) (ch - 'A' + 1);
			num *= Math.pow(26, i);
			result += num;
		}
		return result;
	}

	/**
	 * Excel column index begin 1
	 * 
	 * @param columnIndex
	 * @return
	 */
	public static String excelColIndexToStr(int columnIndex) {
		if (columnIndex <= 0) {
			return null;
		}
		String columnStr = "";
		columnIndex--;
		do {
			if (columnStr.length() > 0) {
				columnIndex--;
			}
			columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
			columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
		} while (columnIndex > 0);
		return columnStr;
	}

	public static void main(String[] args) throws Exception {

		String colstr = "A";
		int colIndex = excelColStrToNum(colstr, colstr.length());
		System.out.println(colIndex);

//		System.out.println(excelColIndexToStr(28));
//		InputStream inp =MinioClientUtils.getObjectToInputStream("d1amrCWa0U7e15pNUlNEYvKcFLLern8Z.xlsx");
////		String filePath = "D://tools//Data//zbak//d1amrCWa0U7e15pNUlNEYvKcFLLern8Z.xlsx";
////		FileInputStream inp = new FileInputStream(filePath); 
//		Workbook wb = new XSSFWorkbook(inp);
//		Sheet sheet = wb.getSheetAt(0); // 获得第三个工作薄(2008工作薄)
//		// 填充上面的表格,数据需要从数据库查询
//		Row row0 = sheet.getRow(0); // 获得工作薄的第五行
//		//获得总列数
//		int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();
//		
//		for(int i=0;i<coloumNum;i++)
//		{
//			System.out.println(excelColIndexToStr(i+1)+":"+row0.getCell(i));
//		}
//		int rowNum=sheet.getLastRowNum();//获得总行数
		
	}
}
